package com.mmy.dao.sys;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.sys.TSysUser;

/**
 * 用户表管理
 *
 * @param 参数说明
 * @return 返回值说明
 * @throws 异常处理说明
 * @author 创建人 hanxl 整理
 * @date 创建日期 2020.10.29
 */
public interface TSysUserRepository extends JpaRepository<TSysUser, Integer>, JpaSpecificationExecutor<TSysUser> {
	/**
	 * 查询所有用户信息 findAll()
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、 用处:注册时搜索所有用户编号检验推荐人是否填写正确 SysUserController中 index函数
	 *       2、注册时查询是否已经注册 SysUserController中register 函数
	 */
	/**
	 * 查询指定用户信息 通过手机号查询
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、 用处:用户登录时用手机号登录 SysUserController中 login函数
	 *       2、注册时查询是否已经注册SysUserController中register 函数
	 * 
	 *       2021.03.17 判断同行人问题 如果手机号存在并且有账号不以visitor开头说明已有注册
	 */
	@Query(value = "select *  from tSysUser where cUserAccount = :cUserMobile and cUserAccount not like \'visitor%\'", nativeQuery = true)
	TSysUser findByUserMobile(@Param("cUserMobile") String strUserMobile);

	/**
	 * 查询指定用户信息 通过邮箱查询
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、 用处:用户登录时用邮箱登录 SysUserController中 login函数
	 *       2、注册时查询是否已经注册SysUserController中register 函数
	 * 
	 * 
	 *       2021.03.17 判断同行人问题 如果手机号存在并且有账号不以visitor开头说明已有注册
	 */
	@Query(value = "select *  from tSysUser where cMail = :cMail and cUserAccount not like \'visitor%\'", nativeQuery = true)
	TSysUser findByEmail(@Param("cMail") String strMail);

	/**
	 * 查询指定用户信息 通过账号查询
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、 用处:用户登录时用账号登录 SysUserController中 login函数
	 *       2、注册时查询是否已经注册SysUserController中register 函数
	 */
	@Query(value = "select * from tSysUser where cUserAccount = :cUserAccount", nativeQuery = true)
	TSysUser findByUserAccount(@Param("cUserAccount") String strUserAccount);

	/**
	 * 查询指定用户信息 通过身份证查询
	 * 
	 * 后天添加员工时使用
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时查询是否已经注册SysUserController中register 函数
	 * 
	 * 
	 *       2021.03.17 考虑同行人问题 如果身份证存在 并且账号名不以visitor开头 说明用户已注册 否则可以注册
	 */
	@Query(value = "select *  from tSysUser where cIdentity = :cIdentity and cUserAccount not like \'visitor%\'", nativeQuery = true)
	TSysUser findByIdentity(@Param("cIdentity") String strIdentity);

	/**
	 * 获得游客用户最大编号
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时用来查询最大编号SysUserController中register 函数
	 *       2、将未通过审核的用户创建成新游客用户SysUserController中toUpdateNotReview()函数
	 */
	@Query(value = "select MAX(iUserID)  from tSysUser where iUserID<200000000", nativeQuery = true)
	Object getMaxMemberUserID();

	/**
	 * 获得员工最大编号
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时用来查询员工最大编号SysUserController中register 函数
	 */
	@Query(value = "select MAX(iUserID)  from tSysUser where iUserID between 210000000 and 219999999", nativeQuery = true)
	Object getMaxStaffUserID();

	/**
	 * 获得员工导游最大编号
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时用来查询员工导游最大编号SysUserController中register 函数
	 */
	@Query(value = "select MAX(iUserID)  from tSysUser where iUserID between 220000000 and 229999999", nativeQuery = true)
	Object getMaxGuideUserID();

	/**
	 * 获得员工司机最大编号
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时用来查询员工司机最大编号SysUserController中register 函数
	 */
	@Query(value = "select MAX(iUserID)  from tSysUser where iUserID between 230000000 and 239999999", nativeQuery = true)
	Object getMaxDriverUserID();

	/**
	 * 获得员工主播最大编号
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册时用来查询员工主播最大编号SysUserController中register 函数
	 */
	@Query(value = "select MAX(iUserID)  from tSysUser where iUserID between 240000000 and 299999999", nativeQuery = true)
	Object getMaxAnchorUserID();

	/**
	 * 注册定义主键ID
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册SysUserController中register 函数
	 * 
	 *       2、景区同行人不存在时创建时使用
	 * 
	 *       后台创建用户 和会员时使用 2021.03.17 hanxl
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tSysUser(iUserID, cUserName, cUserAccount, cPassWord, iUserType, cUserMobile, iOrgID, iUpUserID, bSex, cIdentity, cTel, cFax, cMail, cJob, cCadePath, cPhotoPath, iAccountState, iMemberState, iAddressID, bBlack, bVIP, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (?1, NULL, NULL, Null, 0, NULL, 0, 0, b'0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, b'0', b'0', null, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(int userID);

	/**
	 * 修改游客或者员工用户 save(tSysUser)
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、注册SysUserController中register 函数
	 * 
	 */
	/**
	 * 通过用户ID查找用户
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、 用处:选择用户SysUserController中updateReView()函数
	 * 
	 *       修改员工信息用到2021.03.17 hanxl
	 */
	@Query(value = "select *  from tSysUser where iUserID=?1", nativeQuery = true)
	TSysUser findByID(int iUserID);

	/* 查找所有游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0", nativeQuery = true)
	List<TSysUser> findALLMember();

	/* 查找所有用户 */
	@Query(value = "select *  from tSysUser where iUserType !=0", nativeQuery = true)
	List<TSysUser> findALLStaff();

	/* 查找该手机号的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserMobile=?1", nativeQuery = true)
	List<TSysUser> findMemberByUserMobile(String cUserMobile);

	/* 查找该性别的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and bSex=?1", nativeQuery = true)
	List<TSysUser> findMemberBySex(Byte bSex);

	/* 查找该手机号该性别的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserMobile=?1 and  bSex=?2", nativeQuery = true)
	List<TSysUser> findMemberByUserMobileAndSex(String cUserMobile, Byte bSex);

	/* 查找该姓名的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserName like %?1% ", nativeQuery = true)
	List<TSysUser> findMemberByUserName(String cUserName);

	/* 查找该姓名该手机号的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserName like %?1% and cUserMobile=?2 ", nativeQuery = true)
	List<TSysUser> findMemberByUserNameAndUserMobile(String cUserName, String cUserMobile);

	/* 查找该姓名该性别的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserName like %?1% and bSex=?2 ", nativeQuery = true)
	List<TSysUser> findMemberByUserNameAndSex(String cUserName, Byte bSex);

	/* 查找该姓名该手机号该性别的游客 */
	@Query(value = "select *  from tSysUser where iUserType = 0 and cUserName like %?1%  and cUserMobile=?2 and bSex=?3 ", nativeQuery = true)
	List<TSysUser> findMemberByUserNameAndUserMobileAndSex(String cUserName, String cUserMobile, Byte bSex);

	/* 查找该手机号的用户 */
	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserMobile=?1", nativeQuery = true)
	List<TSysUser> findStaffByUserMobile(String cUserMobile);

	/* 查找该性别的用户 */
	@Query(value = "select *  from tSysUser where iUserType != 0 and bSex=?1", nativeQuery = true)
	List<TSysUser> findStaffBySex(Byte bSex);

	/* 查找该手机号该性别的用户 */
	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserMobile=?1 and  bSex=?2", nativeQuery = true)
	List<TSysUser> findStaffByUserMobileAndSex(String cUserMobile, Byte bSex);

	/* 查找该姓名的用户 */

	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserName like %?1% ", nativeQuery = true)
	List<TSysUser> findStaffByUserName(String cUserName);

	/* 查找该姓名该手机号的用户 */

	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserName like %?1% and cUserMobile=?2 ", nativeQuery = true)
	List<TSysUser> findStaffByUserNameAndUserMobile(String cUserName, String cUserMobile);

	/* 查找该姓名该性别的用户 */

	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserName like %?1% and bSex=?2 ", nativeQuery = true)
	List<TSysUser> findStaffByUserNameAndSex(String cUserName, Byte bSex);

	/* 查找该姓名该手机号该性别的用户 */

	@Query(value = "select *  from tSysUser where iUserType != 0 and cUserName like %?1%  and cUserMobile=?2 and bSex=?3 ", nativeQuery = true)
	List<TSysUser> findStaffByUserNameAndUserMobileAndSex(String cUserName, String cUserMobile, Byte bSex);

	/* 通过ID查找用户 只有一条 用实体类 不用list */

	@Query(value = "select *  from tSysUser where iUserID = ?1", nativeQuery = true)
	List<TSysUser> findByiUserID(int iUserID);

	/* 自己注册未通过审核 删除其员工信息改为游客 */
	@Transactional
	@Modifying
	@Query(value = "delete from tSysUser where iUserID =?1", nativeQuery = true)
	void delByUserID(int iUserID);
	/*
	 * 用户注册 判断用户手机号唯一
	 */

	@Query(value = "select cPassWord  from tSysUser where cUserMobile = :cUserMobile ", nativeQuery = true)
	String findcPassWordBycUserMobile(@Param("cUserMobile") String cUserMobile);

	List<TSysUser> findBycPassWord(String password);

	TSysUser save(List<TSysUser> db_TSysUser);

	@Transactional

	@Modifying

	@Query(value = " SELECT * from tSysUser where tSysUser.cUserName=:cUserNameand  tSysUser.cUserMobile=:cUserMobile", nativeQuery = true)
	List<TSysUser> findBycPassWord(@Param("cUserName") String cUserName, @Param("cUserMobile") String cUserMobile);

	@Transactional
	String deleteBycUserName(String cUserName);

	/**
	 * 删除用户 （加入黑名单）
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间2021.03.17
	 */
	@Transactional
	@Modifying
	@Query(value = "update tSysUser set bBlack=1 where iUserID in :ids", nativeQuery = true)
	void deleteBycUserIds(@Param("ids") List<Integer> ids);

	@Transactional

	@Modifying

	@Query(value = "select  * from tSysUser where tSysUser.iUserID = :newid", nativeQuery = true)
	List<TSysUser> findByiUserId(@Param("newid") String newid);

	@Query(value = "select MAX(iUserID) from tSysUser", nativeQuery = true)
	Object getMaxIUserID();

	@Query(value = "select *  from tSysUser where cIdentity = :cIdentity ", nativeQuery = true)
	List<TSysUser> findBycIdentity(@Param("cIdentity") String cIdentity);

	@Transactional

	@Modifying

	@Query(value = "update tSysUser set tSysUser.cUserMobile=?2 where  tSysUser.iUserID = ?1 ", nativeQuery = true)
	void update(int iUseriD, String cUserMobile);

	/**
	 * 获得最大编号 景区查询同行人 未查到时重新创建
	 * 
	 * @return 返回值说明 最大ID
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.20
	 * 
	 *       后台创建员工使用 2021.03.17
	 */
	@Query(value = "select Max(iUserID) from tSysUser where LEFT(iUserID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxUserID();

	/**
	 * 创建员工时查询登录人所在公司的所有员工
	 * 
	 * @return 返回值说明
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.03.17
	 */
	@Query(value = "select  * from tSysUser where iOrgID = ?1 and iUserType!=0", nativeQuery = true)
	List<TSysUser> findByOrgIDandType(int iOrgID);

	/**
	 * 后台创建会员 查询该公司所有的员工和会员
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间2021.03.17
	 */
	@Query(value = "select  * from tSysUser where iOrgID = ?1", nativeQuery = true)
	List<TSysUser> findByOrgID(int iOrgID);

}
